# coding=utf-8
# 代码文件：chapter3/ch3.2.8.py
from datetime import date

import xlwings as xw

app = xw.App(visible=False, add_book=False)
f = r'data/学生信息.xlsx'
wb = app.books.open(f)

sheet1 = wb.sheets['Sheet1']                # 通过工作表名返回工作表对象

rng = sheet1.range('A1').current_region

END_ROW_NO = rng.last_cell.row + 1          # 结束行号

END_COL_NO = rng.last_cell.column + 1       # 结束列号

# 按行遍历
for row in range(2, END_ROW_NO):
    # 按列遍历
    for col in range(1, END_COL_NO):

        cell = sheet1.range((row, col))        # 获得单元格对象
        # 如果是[生日]列设置日期格式为"yyyy-mm-dd"
        if col == 3:
            cell.number_format = 'yyyy-mm-dd'

        cell.font.size = 10                     # 设置字体的大小
        cell.font.bold = True                   # 设置为粗体
        # 如果是成绩列设置数字格式
        if col == 5:
            cell.number_format = '0.00'

        # 取出单元格数据
        data = cell.options(numbers=int, dates=date).value

        # 判断单元格是否为空值
        if data is None:
            cell.color = (255, 255, 255)          # 设置为白色
            # cell.color = (255, 255, 0)          # 设置为黄色

# 保存修改后的文件另存为[文件学生信息2.xlsx]
f = r'data/学生信息2.xlsx'
wb.save(path=f)

wb.close()
app.quit()

print('Game Over')
